﻿// Copyright 2006 - Ricardo Stuven (rstuven@gmail.com)
//
// This file is part of MsSqlSpatial.
// MsSqlSpatial is free software; you can redistribute it and/or modify
// it under the terms of the GNU Lesser General Public License as published by
// the Free Software Foundation; either version 2 of the License, or
// (at your option) any later version.
// 
// MsSqlSpatial is distributed in the hope that it will be useful,
// but WITHOUT ANY WARRANTY; without even the implied warranty of
// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
// GNU Lesser General Public License for more details.

// You should have received a copy of the GNU Lesser General Public License
// along with MsSqlSpatial; if not, write to the Free Software
// Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA 

using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using MsSqlSpatial;
using GisSharpBlog.NetTopologySuite.Geometries;

public partial class TableValuedFunctions
{
	/// <summary>
	/// 
	/// </summary>
	/// <param name="table_name_1"></param>
	/// <param name="column_name_1"></param>
	/// <param name="table_name_2"></param>
	/// <param name="column_name_2"></param>
	/// <returns>TABLE(oid1 bigint, oid2 bigint)</returns>
	/// <example>
	/// "What is the length of roads fully contained within each municipality?"
	/// 
	///		SELECT m.name, sum(ST.Length(r.the_geom))/1000 AS roads_km
	///		FROM ST.JoinFilterQuery('bc_roads','the_geom','bc_municipality','the_geom') AS q
	///		INNER JOIN bc_roads AS r ON q.oid1 = r.oid
	///		INNER JOIN bc_municipality AS m ON q.oid2 = m.oid
	///		WHERE ST.[Contains](m.the_geom,r.the_geom)=1
	///		GROUP BY m.name
	///		ORDER BY roads_km
	/// 
	/// </example>
	[SqlFunction(
		FillRowMethodName = "FillRowJoinFilterQuery"
		, TableDefinition = "oid1 bigint, oid2 bigint"
		, DataAccess = DataAccessKind.Read
		, SystemDataAccess = SystemDataAccessKind.Read
	)]
	public static IEnumerable JoinFilterQuery(
		SqlString table_name_1,
		SqlString column_name_1,
		SqlString table_name_2,
		SqlString column_name_2)
	{
		List<Int64[]> oids = new List<Int64[]>();

		using (SqlConnection connection = DatabaseHelper.ContextConnection)
		{
			connection.Open();
			using (SqlCommand command = connection.CreateCommand())
			{
				string tableName1 = table_name_1.Value;
				string columnName1 = column_name_1.Value;
				string tableName2 = table_name_2.Value;
				string columnName2 = column_name_2.Value;

				string schemaName1 = "";
				string schemaName2 = "";

				DatabaseHelper.SplitSchemaAndTableName(command, ref tableName1, ref schemaName1);
				DatabaseHelper.SplitSchemaAndTableName(command, ref tableName2, ref schemaName2);

				int count1 = CountTableRows(command, tableName1, schemaName1);
				int count2 = CountTableRows(command, tableName2, schemaName2);

				const string tableAlias1 = "gis_t1";
				const string tableAlias2 = "gis_t2";

				string swappedTableAlias1;
				string swappedTableAlias2;
				string swappedColumnName1;
				string swappedColumnName2;

				// Use indexes of the table with more rows.
				if (count1 > count2)
				{
					swappedTableAlias1 = tableAlias1;
					swappedTableAlias2 = tableAlias2;
					swappedColumnName1 = columnName1;
					swappedColumnName2 = columnName2;
				}
				else
				{
					swappedTableAlias1 = tableAlias2;
					swappedTableAlias2 = tableAlias1;
					swappedColumnName1 = columnName2;
					swappedColumnName2 = columnName1;
				}

				string primaryKey1 = GeometryTable.GetPrimaryKeyColumn(command, schemaName1, tableName1);
				string primaryKey2 = GeometryTable.GetPrimaryKeyColumn(command, schemaName2, tableName2);

				DatabaseHelper.BuildCommand(command, string.Format(
@"SELECT [{0}].[{10}], [{1}].[{11}] 
FROM [{2}].[{3}] as [{0}], [{4}].[{5}] as [{1}]
WHERE (
[{6}].[{7}_Envelope_MinX] < ST.MaxX([{8}].[{9}]) AND
[{6}].[{7}_Envelope_MaxX] > ST.MinX([{8}].[{9}]) AND
[{6}].[{7}_Envelope_MinY] < ST.MaxY([{8}].[{9}]) AND
[{6}].[{7}_Envelope_MaxY] > ST.MinY([{8}].[{9}])
)"
, tableAlias1			// {0}
, tableAlias2			// {1}
, schemaName1			// {2}
, tableName1			// {3}
, schemaName2			// {4}
, tableName2			// {5}
, swappedTableAlias1	// {6}
, swappedColumnName1	// {7}
, swappedTableAlias2	// {8}
, swappedColumnName2	// {9}
, primaryKey1			// {10}
, primaryKey2			// {11}
));

				using (SqlDataReader reader = command.ExecuteReader())
				{
					while (reader.Read())
					{
						oids.Add(new Int64[] { (long)reader.GetValue(0), (long)reader.GetValue(1) });
					}
				}
			}
		}
		return oids;
	}

	private static int CountTableRows(SqlCommand command, string tableName, string schemaName)
	{
		DatabaseHelper.BuildCommand(command, string.Format(
@"SELECT count(*)
FROM [{0}].[{1}]"
, schemaName, tableName));
		return (int)command.ExecuteScalar();
	}

	public static void FillRowJoinFilterQuery(Object obj, out SqlInt64 oid1, out SqlInt64 oid2)
	{
		oid1 = new SqlInt64(((Int64[])obj)[0]);
		oid2 = new SqlInt64(((Int64[])obj)[1]);
	}

};

